package com.roger.cmdkit.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

public class ExcelUtil {
    private static String TAG = "Test" + ExcelUtil.class.getSimpleName();
    public static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd", Locale.CHINA);

    @SuppressWarnings("ResultOfMethodCallIgnored")
    public static void createExcel(String filePath, String[] title) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            HSSFSheet sheet = workbook.createSheet("Report");
            sheet.setDefaultColumnWidth((short)15);
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell;
            font.setFontHeightInPoints((short)12);
            //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setFont(font);
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell((short) i);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                cell.setCellStyle(style);
                cell.setCellValue(title[i]);
            }
            File file = new File(filePath);
            if(!file.exists()){
                file.getParentFile().mkdirs();
                file.createNewFile();
            }
            FileOutputStream stream = new FileOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void addExcelData(String file,int sheet,int rows,short cols,String text) {
        try {
            File f = new File(file);
            FileInputStream fis=new FileInputStream(f);
            BufferedInputStream bis = new BufferedInputStream(fis);
            POIFSFileSystem fs = new POIFSFileSystem(bis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);

            HSSFFont font = wb.createFont();
            font.setFontName(new String("微软雅黑".getBytes("gbk"),"utf-8"));
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            style.setFont(font);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);

            HSSFSheet st = wb.getSheetAt(sheet);
            HSSFRow row = st.createRow(rows);
            HSSFCell cell = row.createCell(cols);
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(style);
            cell.setCellValue(text);

            FileOutputStream fos = new FileOutputStream(file);
            wb.write(fos);
            fos.flush();
            fos.close();
            bis.close();
            fis.close();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    public static int getAllRows(String file,int sheet) {
        try {
            HSSFSheet st = getSheet(file,sheet);
            if(st == null) return 0;
            return st.getLastRowNum();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int getAllCells(String file,int sheet) {
        try {
            HSSFSheet st = getSheet(file,sheet);
            if(st == null) return 0;
            HSSFRow row = st.getRow(0);
            return row.getPhysicalNumberOfCells();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static String getExcelData(String file, int sheet, int rows, short cols)  {
        HSSFSheet st = getSheet(file,sheet);
        if(st == null) return "";
        HSSFRow row = st.getRow(rows);
        HSSFCell cell= row.getCell(cols);
        if (cell != null) {
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            return dateFormat.format(date);
                        }
                    } else {
                        return new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    if (!cell.getStringCellValue().equals("")) {
                        return cell.getStringCellValue();
                    } else {
                        return cell.getNumericCellValue() + "";
                    }
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return  (cell.getBooleanCellValue() ? "Y" : "N");
                default:
                    return "";
            }
        }
        return "";
    }


    public static HSSFSheet getSheet(String file, int sheet){
        try {
            File f = new File(file);
            FileInputStream fis = new FileInputStream(f);
            BufferedInputStream bis = new BufferedInputStream(fis);
            POIFSFileSystem fs = new POIFSFileSystem(bis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            bis.close();
            fis.close();
            return wb.getSheetAt(sheet);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}
